package org.apogee.mil.server.trainee;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Random;

import org.apache.log4j.Logger;
import org.apogee.mil.client.services.trainee.TraineeService;
import org.apogee.mil.server.database.DatabaseDriver;
import org.apogee.mil.server.database.helper.DatabaseHelper;
import org.apogee.mil.shared.Constants.MessagesField;
import org.apogee.mil.shared.Question;
import org.apogee.mil.shared.Constants.AllCdcFields;
import org.apogee.mil.shared.Constants.DisplayFormat;
import org.apogee.mil.shared.Constants.GroupTestLookupFields;
import org.apogee.mil.shared.Constants.GroupUserLookupFields;
import org.apogee.mil.shared.Constants.QuestionFields;
import org.apogee.mil.shared.Constants.TestFields;
import org.apogee.mil.shared.Constants.TrainerTraineeLookupFields;
import org.apogee.mil.shared.Constants.UserLookupFields;

import com.extjs.gxt.ui.client.data.BaseModel;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

public class TraineeServiceImpl extends RemoteServiceServlet implements TraineeService {

	private static Logger logger = Logger.getLogger(TraineeServiceImpl.class);
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private DatabaseDriver db = new DatabaseDriver();


	@Override
	public boolean initialize() {

		db = new DatabaseDriver();
		return db.initialize();
	}


	@Override
	public ArrayList<BaseModel> getAvailableTests(int userId) {
		ArrayList<BaseModel> model = new ArrayList<BaseModel>();
		
		String stmt = String.format("SELECT distinct cdcs.%s, cdcs.%s " +
		"FROM all_cdcs cdcs, group_test_lookup gtl, group_user_lookup gul "+
		"WHERE gtl.%s = gul.%s " +
		"AND gtl.%s = cdcs.%s " +
		"AND gul.%s=%d", AllCdcFields.CDC, AllCdcFields.MIN_SCORE, GroupTestLookupFields.GROUP_ID, GroupUserLookupFields.GROUP_ID,
							 GroupTestLookupFields.TEST_ID, AllCdcFields.TEST_ID, GroupUserLookupFields.USER_ID, userId);

		ResultSet set = db.executeQuery(stmt);

		try {
			while(set.next()) {
				BaseModel bm = new BaseModel();
				bm.set(AllCdcFields.CDC, set.getString(AllCdcFields.CDC));
				bm.set(AllCdcFields.MIN_SCORE, set.getFloat(AllCdcFields.MIN_SCORE));
				model.add(bm);
			}
		} catch (SQLException e) { }

		return model;
	}

	@Override
	public ArrayList<BaseModel> getRecentScores(int userId) {
		ArrayList<BaseModel> model = new ArrayList<BaseModel>();

		String stmt = String.format("SELECT %s, to_char(%s,'990.00') as %s, to_char( %s, '%s' ) as %s " +
				"FROM tests WHERE %s=%d order by %s desc", TestFields.TEST_TAKEN, TestFields.SCORE, TestFields.SCORE, TestFields.DATE_TAKEN, 
										"MM/dd/yyyy", TestFields.DATE_TAKEN, TestFields.USER_ID, userId, TestFields.DATE_TAKEN);

		ResultSet set = db.executeQuery(stmt);

		try {
			while(set.next()) {
				BaseModel bm = new BaseModel();
				bm.set(TestFields.TEST_TAKEN, set.getString(TestFields.TEST_TAKEN));
				bm.set(TestFields.SCORE, set.getString(TestFields.SCORE));
				bm.set(TestFields.DATE_TAKEN, set.getString(TestFields.DATE_TAKEN));
				model.add(bm);
			}

		} catch (SQLException e) { }

		return model;
	}


	@Override
	public ArrayList<BaseModel> getTestVolumes(String test) {
		
		try {
			Integer.parseInt(test.substring(0,1));
			test = "A"+test;
		} catch (NumberFormatException ex) { }

		
		String stmt = String.format("select distinct %s from %s order by %s asc", QuestionFields.VOLUME, test, QuestionFields.VOLUME);
		ResultSet set = db.executeQuery(stmt);

		ArrayList<BaseModel> volumes = new ArrayList<BaseModel>();
		
		BaseModel bm = new BaseModel();
		String volume = "0";
		String description = volume + " - All";
		
		bm.set(QuestionFields.VOLUME, volume);
		bm.set(DisplayFormat.VOLUME_FMT, description);
		
		volumes.add(bm);
		
		try {
			while(set.next()) {
				bm = new BaseModel();
				volume = set.getString(QuestionFields.VOLUME);
				description = volume + " - Volume " + volume;
				bm.set(QuestionFields.VOLUME, volume);
				bm.set(DisplayFormat.VOLUME_FMT, description);
				
				volumes.add(bm);
			}
		} catch (SQLException e) {
			logger.error("Unable to get volumes for test " +test);
			return new ArrayList<BaseModel>();
		}

		return volumes;
	}

	@Override
	public List<Question> getQuestions(String test, int numQuestions,
			int volume, int reviewType) {
		String stmt = DatabaseHelper.getSelectedQuestions(test, volume, reviewType);
		ResultSet set = db.executeQuery(stmt);

		List<Question> q = new ArrayList<Question>();

		Random random = new Random();

		try {
			while (set.next()) {
				ArrayList<String> choices = new ArrayList<String>();
				choices.add(set.getString("choice1"));
				choices.add(set.getString("choice2"));
				choices.add(set.getString("choice3"));
				choices.add(set.getString("choice4"));
				Question question = new Question();

				question.setQuestion(set.getString("question"));
				question.setChoices(choices);
				question.setAnswer(set.getInt("answer"));
				question.setRandomId(random.nextInt(Integer.MAX_VALUE));
				question.setId(set.getInt("id"));

				q.add(question);
			}
		} catch (SQLException e) {
			logger.error("Unable to get questions" + e);
			return new ArrayList<Question>();
		}

		Collections.sort(q);

		numQuestions = ((numQuestions > q.size()) || (numQuestions <= 0)) ? q.size() : numQuestions;


		List<Question> questions = new ArrayList<Question>();

		for (int i = 0; i < numQuestions; i++) {
			questions.add(q.get(i));
		}


		return questions;
	}

	@Override
	public boolean addResults(int userid, String test, float score) {
		return db.addResults(userid, test, score);
	}
	
	@Override
	public String getTrainer(int userId) {
		String stmt = String.format("select %s, %s, %s from user_lookup where %s in (" +
									"select distinct %s from trainer_trainee_lookup where %s = %d)", UserLookupFields.USER_ID, 
									UserLookupFields.FIRST_NAME, UserLookupFields.LAST_NAME, UserLookupFields.USER_ID,
									TrainerTraineeLookupFields.TRAINER_ID, TrainerTraineeLookupFields.TRAINEE_ID, userId);
		
		ResultSet set = db.executeQuery(stmt);
		
		String name = "";
		
		try {
			if(set.next()) {
				name = set.getString(UserLookupFields.USER_ID) + " - " + set.getString(UserLookupFields.FIRST_NAME) + " " + set.getString(UserLookupFields.LAST_NAME);
			}
		} catch (SQLException e) {
			logger.error(e);
		}
		
		return name;
	}


	@Override
	public boolean addMessage(int to_user, int from_user, String message) {
		String[] fields = MessagesField.MESSAGE_FIELDS;
		String[] values = {"", String.valueOf(to_user), String.valueOf(from_user), message, "sysdate"};
		
		String stmt = DatabaseHelper.createInsertStatement("messages", fields, values);
		
		return db.executeUpdate(stmt);
	}

	@Override
	public boolean close() {

		return db.close();
	}


}
